package cn.gov.pbc.tims.controller;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class test {
 
	/**
	 * @param args
	 * @throws IOException 
	 * @throws FileNotFoundException 
	 */
	public static void main(String[] args) throws FileNotFoundException, IOException {
		String excelUrl = "D:/我的文档/判断题.xls";
		//work1(excelUrl);
		//deletEmptyline(excelUrl);
		panduan(excelUrl);
	}
	public static void panduan(String excelUrl)throws FileNotFoundException, IOException 
	{
		HSSFWorkbook hssfworkbook = new HSSFWorkbook(new FileInputStream(
				excelUrl));
		// 遍历该表格中所有的工作表，i表示工作表的数量 getNumberOfSheets表示工作表的总数
		HSSFSheet hssfsheet = hssfworkbook.getSheetAt(0);
		Integer tihao=1;//题号
		HSSFRow  tiganRow=hssfsheet.getRow(1); //题干row
		Integer cellNum=0;
		//String pattern="^/d+/.";
		Pattern pt = Pattern.compile("^\\d+\\s*\\.");
		
		// 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数
				for (int j = 1; j < hssfsheet.getPhysicalNumberOfRows();j++) {
					
					HSSFRow hssfrow = hssfsheet.getRow(j);
					HSSFCell cell0=hssfrow.getCell(0);
					String cell0Value=cell0.getStringCellValue();
					Matcher matcher = pt.matcher(cell0Value);
					if(matcher.find())
					{
						cellNum=0;
						//以1.  2. 3. 打头的行为题干。记住题干所在的行数。
						tiganRow=hssfrow;
						int inde=cell0Value.indexOf(".");
						hssfrow.getCell(0).setCellValue(tihao.toString()+".");
						cellNum++;
						HSSFCell cell1=hssfrow.createCell(cellNum);
						cell1.setCellValue(cell0Value.substring(inde+1,cell0Value.trim().length()-3));
						cellNum++;
						HSSFCell cell2=hssfrow.createCell(cellNum);
						cell2.setCellValue(cell0Value.substring(cell0Value.trim().length()-3));
						tihao++;
						
					}
				}
				FileOutputStream os = new FileOutputStream(excelUrl);  
				hssfworkbook.write(os);
	            os.close();  
	}
	
	public static void work1(String excelUrl)throws FileNotFoundException, IOException 
	{
		HSSFWorkbook hssfworkbook = new HSSFWorkbook(new FileInputStream(
				excelUrl));
		// 遍历该表格中所有的工作表，i表示工作表的数量 getNumberOfSheets表示工作表的总数
		HSSFSheet hssfsheet = hssfworkbook.getSheetAt(0);
		Integer tihao=1;//题号
		HSSFRow  tiganRow=hssfsheet.getRow(1); //题干row
		Integer cellNum=0;
		//String pattern="^/d+/.";
		Pattern pt = Pattern.compile("^\\d+\\s*\\.");
		
		// 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数
				for (int j = 1; j < hssfsheet.getPhysicalNumberOfRows();j++) {
					
					HSSFRow hssfrow = hssfsheet.getRow(j);
					HSSFCell cell0=hssfrow.getCell(0);
					String cell0Value=cell0.getStringCellValue();
					Matcher matcher = pt.matcher(cell0Value);
					if(matcher.find())
					{
						cellNum=0;
						//以1.  2. 3. 打头的行为题干。记住题干所在的行数。
						tiganRow=hssfrow;
						int inde=cell0Value.indexOf(".");
						hssfrow.getCell(0).setCellValue(tihao.toString()+".");
						cellNum++;
						HSSFCell cell1=hssfrow.createCell(cellNum);
						cell1.setCellValue(cell0Value.substring(inde+1));
						tihao++;
						
					}
					else if (cell0Value.startsWith("A.")||cell0Value.startsWith("B.")||cell0Value.startsWith("C.")||cell0Value.startsWith("D.")||cell0Value.startsWith("E.")||cell0Value.startsWith("F."))
					 {
						
						cellNum++;
						cell0.setCellValue("");
						HSSFCell cell = tiganRow.createCell(cellNum);
						cell.setCellValue(cell0Value);
						
							}
					else if(cell0Value.startsWith("答"))
						 {
						cell0.setCellValue("");
						HSSFCell cell = tiganRow.createCell(8);
						cell.setCellValue(cell0Value);
						 }
						
				}
				FileOutputStream os = new FileOutputStream(excelUrl);  
				hssfworkbook.write(os);
	            os.close();  
	}
	public static void deletEmptyline(String excelUrl)throws FileNotFoundException, IOException 
	{
		HSSFWorkbook hssfworkbook = new HSSFWorkbook(new FileInputStream(
				excelUrl));
		// 遍历该表格中所有的工作表，i表示工作表的数量 getNumberOfSheets表示工作表的总数
		HSSFSheet hssfsheet = hssfworkbook.getSheetAt(0);
		Pattern pt = Pattern.compile("^\\d+\\.");
		int i = hssfsheet.getLastRowNum();
		HSSFRow tempRow;
		while(i > 1){
		i--;
		tempRow = hssfsheet.getRow(i);
		if(tempRow == null){
			hssfsheet.shiftRows(i+1, hssfsheet.getLastRowNum(), -1);
		}
		else
		{
			HSSFCell cell0=tempRow.getCell(0);
			String cell0Value=cell0.getStringCellValue();
			Matcher matcher = pt.matcher(cell0Value);
			if(matcher.find())
			{}
			else
			hssfsheet.shiftRows(i+1, hssfsheet.getLastRowNum(), -1);
		}
		}

		FileOutputStream os = new FileOutputStream(excelUrl);  
		hssfworkbook.write(os);
	    os.close();  
	}
} 
